CREATE TABLE [dbo].[Trans]
(
[TRANS_NUMBER] [int] NOT NULL CONSTRAINT [DF_Trans_TRANS_NUMBER] DEFAULT ((0)),
[LINE_NUMBER] [int] NOT NULL CONSTRAINT [DF_Trans_LINE_NUMBER] DEFAULT ((0)),
[BATCH_NUM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_BATCH_NUM] DEFAULT (''),
[OWNER_ORG_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_OWNER_ORG_CODE] DEFAULT (''),
[SOURCE_SYSTEM] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_SOURCE_SYSTEM] DEFAULT (''),
[JOURNAL_TYPE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_JOURNAL_TYPE] DEFAULT (''),
[TRANSACTION_TYPE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_TRANSACTION_TYPE] DEFAULT (''),
[TRANSACTION_DATE] [datetime] NOT NULL,
[BT_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_BT_ID] DEFAULT (''),
[ST_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_ST_ID] DEFAULT (''),
[INVOICE_REFERENCE_NUM] [int] NOT NULL CONSTRAINT [DF_Trans_INVOICE_REFERENCE_NUM] DEFAULT ((0)),
[DESCRIPTION] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_DESCRIPTION] DEFAULT (''),
[CUSTOMER_NAME] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_CUSTOMER_NAME] DEFAULT (''),
[CUSTOMER_REFERENCE] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_CUSTOMER_REFERENCE] DEFAULT (''),
[REFERENCE_1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_REFERENCE_1] DEFAULT (''),
[SOURCE_CODE] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_SOURCE_CODE] DEFAULT (''),
[PRODUCT_CODE] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_PRODUCT_CODE] DEFAULT (''),
[EFFECTIVE_DATE] [datetime] NULL,
[PAID_THRU] [datetime] NULL,
[MONTHS_PAID] [int] NOT NULL CONSTRAINT [DF_Trans_MONTHS_PAID] DEFAULT ((0)),
[FISCAL_PERIOD] [int] NOT NULL CONSTRAINT [DF_Trans_FISCAL_PERIOD] DEFAULT ((0)),
[DEFERRAL_MONTHS] [int] NOT NULL CONSTRAINT [DF_Trans_DEFERRAL_MONTHS] DEFAULT ((0)),
[AMOUNT] [money] NOT NULL CONSTRAINT [DF_Trans_AMOUNT] DEFAULT ((0)),
[ADJUSTMENT_AMOUNT] [money] NOT NULL CONSTRAINT [DF_Trans_ADJUSTMENT_AMOUNT] DEFAULT ((0)),
[PSEUDO_ACCOUNT] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_PSEUDO_ACCOUNT] DEFAULT (''),
[GL_ACCT_ORG_CODE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_GL_ACCT_ORG_CODE] DEFAULT (''),
[GL_ACCOUNT] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_GL_ACCOUNT] DEFAULT (''),
[DEFERRED_GL_ACCOUNT] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_DEFERRED_GL_ACCOUNT] DEFAULT (''),
[INVOICE_CHARGES] [money] NOT NULL CONSTRAINT [DF_Trans_INVOICE_CHARGES] DEFAULT ((0)),
[INVOICE_CREDITS] [money] NOT NULL CONSTRAINT [DF_Trans_INVOICE_CREDITS] DEFAULT ((0)),
[QUANTITY] [numeric] (15, 4) NOT NULL CONSTRAINT [DF_Trans_QUANTITY] DEFAULT ((0)),
[UNIT_PRICE] [money] NOT NULL CONSTRAINT [DF_Trans_UNIT_PRICE] DEFAULT ((0)),
[PAYMENT_TYPE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_PAYMENT_TYPE] DEFAULT (''),
[CHECK_NUMBER] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_CHECK_NUMBER] DEFAULT (''),
[CC_NUMBER] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_CC_NUMBER] DEFAULT (''),
[CC_EXPIRE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_CC_EXPIRE] DEFAULT (''),
[CC_AUTHORIZE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_CC_AUTHORIZE] DEFAULT (''),
[CC_NAME] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_CC_NAME] DEFAULT (''),
[TERMS_CODE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_TERMS_CODE] DEFAULT (''),
[ACTIVITY_SEQN] [int] NOT NULL CONSTRAINT [DF_Trans_ACTIVITY_SEQN] DEFAULT ((0)),
[POSTED] [tinyint] NOT NULL CONSTRAINT [DF_Trans_POSTED] DEFAULT ((0)),
[PROD_TYPE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_PROD_TYPE] DEFAULT (''),
[ACTIVITY_TYPE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_ACTIVITY_TYPE] DEFAULT (''),
[ACTION_CODES] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_ACTION_CODES] DEFAULT (''),
[TICKLER_DATE] [datetime] NULL,
[DATE_ENTERED] [datetime] NULL,
[ENTERED_BY] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_ENTERED_BY] DEFAULT (''),
[SUB_LINE_NUMBER] [int] NOT NULL CONSTRAINT [DF_Trans_SUB_LINE_NUMBER] DEFAULT ((0)),
[INSTALL_BILL_DATE] [datetime] NULL,
[TAXABLE_VALUE] [money] NOT NULL CONSTRAINT [DF_Trans_TAXABLE_VALUE] DEFAULT ((0)),
[SOLICITOR_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_SOLICITOR_ID] DEFAULT (''),
[INVOICE_ADJUSTMENTS] [money] NOT NULL CONSTRAINT [DF_Trans_INVOICE_ADJUSTMENTS] DEFAULT ((0)),
[INVOICE_LINE_NUM] [int] NOT NULL CONSTRAINT [DF_Trans_INVOICE_LINE_NUM] DEFAULT ((0)),
[MERGE_CODE] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_MERGE_CODE] DEFAULT (''),
[SALUTATION_CODE] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_SALUTATION_CODE] DEFAULT (''),
[SENDER_CODE] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_SENDER_CODE] DEFAULT (''),
[IS_MATCH_GIFT] [tinyint] NOT NULL CONSTRAINT [DF_Trans_IS_MATCH_GIFT] DEFAULT ((0)),
[MATCH_GIFT_TRANS_NUM] [int] NOT NULL CONSTRAINT [DF_Trans_MATCH_GIFT_TRANS_NUM] DEFAULT ((0)),
[MATCH_ACTIVITY_SEQN] [int] NOT NULL CONSTRAINT [DF_Trans_MATCH_ACTIVITY_SEQN] DEFAULT ((0)),
[MEM_TRIB_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_MEM_TRIB_ID] DEFAULT (''),
[RECEIPT_ID] [int] NOT NULL CONSTRAINT [DF_Trans_RECEIPT_ID] DEFAULT ((0)),
[DO_NOT_RECEIPT] [tinyint] NOT NULL CONSTRAINT [DF_Trans_DO_NOT_RECEIPT] DEFAULT ((0)),
[CC_STATUS] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_CC_STATUS] DEFAULT (''),
[ENCRYPT_CC_NUMBER] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_ENCRYPT_CC_NUMBER] DEFAULT (''),
[ENCRYPT_CC_EXPIRE] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_ENCRYPT_CC_EXPIRE] DEFAULT (''),
[FR_ACTIVITY] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_FR_ACTIVITY] DEFAULT (''),
[FR_ACTIVITY_SEQN] [int] NOT NULL CONSTRAINT [DF_Trans_FR_ACTIVITY_SEQN] DEFAULT ((0)),
[MEM_TRIB_NAME_TEXT] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_MEM_TRIB_NAME_TEXT] DEFAULT (''),
[CAMPAIGN_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_CAMPAIGN_CODE] DEFAULT (''),
[IS_FR_ITEM] [bit] NOT NULL CONSTRAINT [DF_Trans_IS_FR_ITEM] DEFAULT ((0)),
[ENCRYPT_CSC] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_ENCRYPT_CSC] DEFAULT (''),
[ISSUE_DATE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_ISSUE_DATE] DEFAULT (''),
[ISSUE_NUMBER] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_ISSUE_NUMBER] DEFAULT (''),
[GL_EXPORT_DATE] [datetime] NULL,
[FR_CHECKBOX] [bit] NOT NULL CONSTRAINT [DF_Trans_FR_CHECKBOX] DEFAULT ((0)),
[GATEWAY_REF] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_GATEWAY_REF] DEFAULT (''),
[TAX_AUTHORITY] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Trans_TAX_AUTHORITY] DEFAULT (''),
[TAX_RATE] [numeric] (15, 4) NOT NULL CONSTRAINT [DF_Trans_TAX_RATE] DEFAULT ((0)),
[TAX_1] [numeric] (15, 4) NOT NULL CONSTRAINT [DF_Trans_TAX_1] DEFAULT ((0)),
[PRICE_ADJ] [bit] NOT NULL CONSTRAINT [DF_Trans_PRICE_ADJ] DEFAULT ((0)),
[TIME_STAMP] [timestamp] NULL
) ON [PRIMARY]

GO
CREATE TRIGGER [dbo].[asi_Trans_DeleteInsertUpdate]
    ON [dbo].[Trans]
    FOR INSERT, UPDATE, DELETE
AS
BEGIN
    INSERT TransWatch (TransactionNumber, InvoiceNumber, CaptureDate)
    SELECT DISTINCT a.TRANS_NUMBER, a.INVOICE_REFERENCE_NUM, getDate()
    FROM ( SELECT TRANS_NUMBER, INVOICE_REFERENCE_NUM
           FROM deleted
           UNION
           SELECT TRANS_NUMBER, INVOICE_REFERENCE_NUM
           FROM inserted
           UNION
           SELECT Invoice.ORIGINATING_TRANS_NUM, deleted.INVOICE_REFERENCE_NUM
           FROM deleted INNER JOIN Invoice ON deleted.INVOICE_REFERENCE_NUM = Invoice.REFERENCE_NUM
           UNION
           SELECT Invoice.ORIGINATING_TRANS_NUM, inserted.INVOICE_REFERENCE_NUM
           FROM inserted INNER JOIN Invoice ON inserted.INVOICE_REFERENCE_NUM = Invoice.REFERENCE_NUM
         ) a
     LEFT JOIN TransWatch ON a.TRANS_NUMBER = TransWatch.TransactionNumber AND
                             a.INVOICE_REFERENCE_NUM = TransWatch.InvoiceNumber
    WHERE TransWatch.TransactionNumber IS NULL
END

GO
ALTER TABLE [dbo].[Trans] ADD CONSTRAINT [PK_Trans] PRIMARY KEY CLUSTERED ([TRANS_NUMBER], [LINE_NUMBER], [SUB_LINE_NUMBER]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransBATCH_NUMBER] ON [dbo].[Trans] ([BATCH_NUM]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransBT_ID] ON [dbo].[Trans] ([BT_ID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransCHECK_NUMBER] ON [dbo].[Trans] ([CHECK_NUMBER]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransDO_NOT_RECEIPT] ON [dbo].[Trans] ([DO_NOT_RECEIPT]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransGL_ACCT_ORG_CODE] ON [dbo].[Trans] ([GL_ACCT_ORG_CODE]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransGL_EXPORT_DATE] ON [dbo].[Trans] ([GL_EXPORT_DATE]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransINSTALL_BILL_DATE] ON [dbo].[Trans] ([INSTALL_BILL_DATE]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransINVOICE_REF_NUM] ON [dbo].[Trans] ([INVOICE_REFERENCE_NUM]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransIS_MATCH_GIFT] ON [dbo].[Trans] ([IS_MATCH_GIFT]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransJOURNAL_TYPE] ON [dbo].[Trans] ([JOURNAL_TYPE]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransLINE_NUMBER] ON [dbo].[Trans] ([LINE_NUMBER]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransMATCH_ACTIVITY_SEQN] ON [dbo].[Trans] ([MATCH_ACTIVITY_SEQN]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransMATCH_GIFT_TRANS_NUM] ON [dbo].[Trans] ([MATCH_GIFT_TRANS_NUM]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransMEM_TRIB_ID] ON [dbo].[Trans] ([MEM_TRIB_ID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransACKNOWLEDGE_CODE] ON [dbo].[Trans] ([MERGE_CODE]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransOWNER_ORG_CODE] ON [dbo].[Trans] ([OWNER_ORG_CODE]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransPOSTED] ON [dbo].[Trans] ([POSTED]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransPRODUCT_CODE] ON [dbo].[Trans] ([PRODUCT_CODE]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransRECEIPT_ID] ON [dbo].[Trans] ([RECEIPT_ID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransSOLICITOR_ID] ON [dbo].[Trans] ([SOLICITOR_ID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransSOURCE_SYSTEM] ON [dbo].[Trans] ([SOURCE_SYSTEM]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransST_ID] ON [dbo].[Trans] ([ST_ID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransSUB_LINE_NUMBER] ON [dbo].[Trans] ([SUB_LINE_NUMBER]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransTRANSACTION_DATE] ON [dbo].[Trans] ([TRANSACTION_DATE]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iTransTRANSACTION_TYPE] ON [dbo].[Trans] ([TRANSACTION_TYPE]) ON [PRIMARY]
GO
GRANT REFERENCES ON  [dbo].[Trans] TO [IMIS]
GRANT SELECT ON  [dbo].[Trans] TO [IMIS]
GRANT INSERT ON  [dbo].[Trans] TO [IMIS]
GRANT DELETE ON  [dbo].[Trans] TO [IMIS]
GRANT UPDATE ON  [dbo].[Trans] TO [IMIS]
GO